/*
 * Copyright (C), 2014-2015, 杭州小卡科技有限公司
 * Created by ciwei@xiaokakeji.com on 2016/06/05.
 */
package com.micromall.repository;

import com.micromall.repository.entity.ReportInfo;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.ResultType;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.util.Date;
import java.util.List;

/**
 * @author ciwei@xiaokakeji.com
 * @date 2016/06/05.
 */
@Repository
public interface AdminStatisticsMapper {

	@Select("SELECT count(*) FROM member WHERE deleted=0")
	@ResultType(Integer.class)
	int getMemberTotalNumber();

	@Select("SELECT count(*) FROM goods WHERE deleted=0")
	@ResultType(Integer.class)
	int getGoodsTotalNumber();

	@Select("SELECT count(*) FROM orders WHERE status IN(1,2,3,4)")
	@ResultType(Integer.class)
	int getOrderTotalNumber();

	@Select("SELECT IFNULL(sum(total_amount),0) FROM orders WHERE status IN(1,2,3,4)")
	@ResultType(BigDecimal.class)
	BigDecimal getTotalSales();

	@Select("SELECT IFNULL(sum(amount),0) FROM withdraw_record WHERE status = 1")
	@ResultType(BigDecimal.class)
	BigDecimal getTotalWithdraw();

	@Select("SELECT DATE_FORMAT(create_time, '%Y-%m-%d') 'time', COUNT(*) 'count' FROM orders WHERE status in(1,2,3,4) AND DATE_FORMAT(create_time, "
			+ "'%Y-%m-%d') >= #{startDate} AND DATE_FORMAT(create_time, '%Y-%m-%d') <= #{endDate} GROUP BY DATE_FORMAT(create_time, '%Y-%m-%d') "
			+ "ORDER BY create_time ASC")
	@ResultType(ReportInfo.class)
	List<ReportInfo> getOrderReport(@Param("startDate") Date startDate, @Param("endDate") Date endDate);

	@Select("SELECT DATE_FORMAT(register_time, '%Y-%m-%d') 'time', COUNT(*) 'count' FROM member WHERE DATE_FORMAT(register_time, '%Y-%m-%d') >= "
			+ "#{startDate} AND DATE_FORMAT(register_time, '%Y-%m-%d') <= #{endDate} GROUP BY DATE_FORMAT(register_time, '%Y-%m-%d') ORDER BY "
			+ "register_time ASC")
	@ResultType(ReportInfo.class)
	List<ReportInfo> getMemberReport(@Param("startDate") Date startDate, @Param("endDate") Date endDate);
}
